# -*- coding: utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.engine import Engine, ResultProxy, RowProxy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from py_engine import engine

import logging

logger = logging.getLogger(__name__)

Session = sessionmaker(bind=engine, )


def sql_update():
    session = Session()
    # SQL语句中参数使用冒号:标记
    sql = """
           update table_name 
           set column_name = :column_name
           where primary_key = :primary_key

        """
    params = {
        'column_name': 'chench',
        'primary_key': 1,
    }
    result = session.execute(sql, params=params)
    logger.info(type(result))
    logger.info(result.rowcount)
    session.commit()
    session.close()

    param_value = ''
    sql = """
        select * from table_name where column_name = :param_key
    """
    session.execute(sql, params={'param_key': param_value})

    pass


def sql_select():
    session = Session()
    sql = """
        select * from table_name
        where primary_key in :primary_key_tuple

    """
    # in参数传入tuple
    primary_key_tuple: tuple = (1, 2,)
    params = {
        'primary_key_tuple': primary_key_tuple
    }
    result_proxy: ResultProxy = session.execute(sql, params=params)
    # result_list: list = result_proxy.fetchall()

    # 处理返回结果，只能读取一次，类似Stream
    result_list = list()
    for row_proxy in result_proxy:
        # row_proxy: RowProxy
        result_list.append({column_name: row_proxy[column_name] for column_name in row_proxy.keys()})
        # for column_name in row_proxy.keys():
        #     logger.info(f'{column_name}:{row_proxy[column_name]}')
        #     pass
        pass

    session.close()

    pass


if __name__ == '__main__':
    sql_select()

    pass
